const express = require('express');
const router = express.Router();
const db = require('../config/database');
const { body, validationResult } = require('express-validator');
const { authenticateToken, generateString, encryptPhone, decryptPhone, extractNumber, logUserOperation } = require('../untils/untils');
const dayjs = require('dayjs')
const { cryptoHelper } = require('../config/global');

// 获取预约记录
// 参数 cust_id=C00001&project_id=PR00002&emp_id=MG00001&status=%E5%BE%85%E7%A1%AE%E8%AE%A4&appoint_time[]=2025-07-15+01:07:37&appoint_time[]=2025-07-26+01:07:38
router.get('/', authenticateToken, async (req, res) => {
    try {
        const { project_id, cust_id, emp_id, status, appoint_time } = req.query;
        let sql = 'SELECT * FROM appointment WHERE 1=1';
        if (project_id) {
            sql += ` AND project_id = '${project_id}'`;
        }
        if (cust_id) {
            sql += ` AND cust_id = '${cust_id}'`;
        }
        if (emp_id) {
            sql += ` AND emp_id = '${emp_id}'`;
        }
        if (status) {
            sql += ` AND status = '${status}'`;
        }
        if (appoint_time) {
            sql += ` AND appoint_time >= '${appoint_time[0]}' AND appoint_time <= '${appoint_time[1]}'`;
        }
        let [result] = await db.execute(sql);

        // 获取客户姓名
        const [customers] = await db.execute(
            'SELECT cust_id, cust_name FROM customer'
        );
        // 获取项目名称
        const [projects] = await db.execute(
            'SELECT project_id, project_name FROM project'
        );
        // 获取员工姓名
        const [employees] = await db.execute(
            'SELECT emp_id, emp_name FROM employee'
        );

        // 获取创建人姓名 连表查询 根据username查询user表的emp_id 再根据emp_id查询employee表的emp_name
        const [creators] = await db.execute(
            'SELECT user.username, employee.emp_name FROM user LEFT JOIN employee ON user.emp_id = employee.emp_id'
        );

        console.log(creators[0]);



        result.forEach(item => {
            item.appoint_time = dayjs(item.appoint_time).format('YYYY-MM-DD HH:mm');
            item.cust = customers.find(customer => customer.cust_id === item.cust_id);
            item.project = projects.find(project => project.project_id === item.project_id);
            item.emp = employees.find(employee => employee.emp_id === item.emp_id);
            item.create_time = dayjs(item.create_time).format('YYYY-MM-DD HH:mm:ss');
            item.creator_name = creators.find(creator => creator.username === item.creator);
        });

        await logUserOperation(req, '查询', '获取预约记录', '获取预约记录成功', '成功');

        res.json({
            success: true,
            message: '获取预约记录成功',
            data: result
        });
    } catch (error) {
        console.error('获取预约记录失败:', error);
        await logUserOperation(req, '查询', '获取预约记录', '获取预约记录失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '获取预约记录失败',
            error: error.message
        });
    }
});

// 新增预约记录
router.put("/add", authenticateToken, async (req, res) => {
    try {
        const { appoint_time, cust_id, emp_id, notes, project_id, status } = req.body;
        const create_time = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const creator = req.user.userId;
        // 获取数据库中最大的category_id TY00001
        const [maxAppointId] = await db.execute(
            'SELECT MAX(appoint_id) AS max_appoint_id FROM appointment'
        );

        let nextId = 1;
        if (maxAppointId[0].max_appoint_id) {
            // 提取数字部分并加1
            const currentNumber = extractNumber(maxAppointId[0].max_appoint_id);
            nextId = currentNumber + 1;
        }

        // 判断该医生的预约时间前后30分钟是否存在, 如果存在则提示, 切状态不等于已取消
        // 步长为30分钟
        const step = 30;
        const [checkAppointTime] = await db.execute(
            'SELECT * FROM appointment WHERE emp_id = ? AND appoint_time >= ? AND appoint_time <= ? AND status != "已取消"',
            [emp_id, dayjs(appoint_time).subtract(step, 'minute').format('YYYY-MM-DD HH:mm:ss'), dayjs(appoint_time).add(step, 'minute').format('YYYY-MM-DD HH:mm:ss')]
        );
        if (checkAppointTime.length > 0) {
            res.status(400).json({
                success: false,
                message: '该医生的预约时间已存在, 请重新选择预约时间'
            });
            return;
        }

        const appoint_id = generateString('T', nextId, 5);
        let sql = `INSERT INTO appointment (appoint_id, appoint_time, cust_id, emp_id, notes, project_id, status, create_time, creator) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        let params = [appoint_id, appoint_time, cust_id, emp_id, notes, project_id, status, create_time, creator];
        const [result] = await db.execute(sql, params);
        await logUserOperation(req, '添加', '新增预约记录', '新增预约记录成功', '成功');

        res.json({
            success: true,
            message: '新增预约记录成功',
            data: result
        });
    } catch (error) {
        console.error('新增预约记录失败:', error);
        await logUserOperation(req, '添加', '新增预约记录', '新增预约记录失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '新增预约记录失败',
            error: error.message
        });
    }
});

// 更新预约记录
router.post("/update", authenticateToken, async (req, res) => {
    try {
        const { cust_id, emp_id, notes, project_id, status, appoint_id, appoint_time } = req.body;
        let sql = `UPDATE appointment SET cust_id = ?, emp_id = ?, notes = ?, project_id = ?, status = ?, appoint_time = ? WHERE appoint_id = ?`;
        let params = [cust_id, emp_id, notes, project_id, status, appoint_time, appoint_id];
        const [result] = await db.execute(sql, params);
        await logUserOperation(req, '更新', '更新预约记录', '更新预约记录成功', '成功');
        res.json({
            success: true,
            message: '更新预约记录成功',
            data: result
        });
    } catch (error) {
        console.error('更新预约记录失败:', error);
        await logUserOperation(req, '更新', '更新预约记录', '更新预约记录失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '更新预约记录失败',
            error: error.message
        });
    }
});

// 删除预约记录
router.delete("/delete", authenticateToken, async (req, res) => {
    try {
        const { appoint_id } = req.query;
        let sql = `DELETE FROM appointment WHERE appoint_id = ?`;
        let params = [appoint_id];
        const [result] = await db.execute(sql, params);
        await logUserOperation(req, '删除', '删除预约记录', '删除预约记录成功', '成功');
        res.json({
            success: true,
            message: '删除预约记录成功',
            data: result
        });
    } catch (error) {
        console.error('删除预约记录失败:', error);
        await logUserOperation(req, '删除', '删除预约记录', '删除预约记录失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '删除预约记录失败',
            error: error.message
        });
    }
});

// 获取客户表数据
router.get("/getCustomerInfo", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT * FROM customer`;
        let [result] = await db.execute(sql);
        result = result.map(item => {
            return {
                key: item.cust_id,
                name: item.cust_name
            }
        });
        res.json({
            success: true,
            message: '获取客户信息成功',
            data: result
        });
    } catch (error) {
        console.error('获取客户信息失败:', error);
        res.status(500).json({
            success: false,
            message: '获取客户信息失败',
            error: error.message
        });
    }
});

// 获取项目表数据
router.get("/getProject", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT project_id as 'key', project_name as 'name' FROM project`;
        let [result] = await db.execute(sql);
        res.json({
            success: true,
            message: '获取项目信息成功',
            data: result
        });
    } catch (error) {
        console.error('获取项目信息失败:', error);
        res.status(500).json({
            success: false,
            message: '获取项目信息失败',
            error: error.message
        });
    }
});

// 获取用户表数据
router.get("/getEmployee", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT emp_id as 'key', emp_name as 'name' FROM employee`;
        let [result] = await db.execute(sql);
        res.json({
            success: true,
            message: '获取用户信息成功',
            data: result
        });
    } catch (error) {
        console.error('获取用户信息失败:', error);
        res.status(500).json({
            success: false,
            message: '获取用户信息失败',
            error: error.message
        });
    }
});

// 获取预约状态
router.get("/getAppointmentStatus", authenticateToken, async (req, res) => {
    let status = [
        {
            key: '待确认',
            name: '待确认'
        },
        {
            key: '已确认',
            name: '已确认'
        },
        {
            key: '已取消',
            name: '已取消'
        }
    ]
    res.json({
        success: true,
        message: '获取预约状态成功',
        data: status
    });
});

// 获取查询项客户list
router.get("/getCustomerList", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT DISTINCT appointment.cust_id as 'key', customer.cust_name as 'name' FROM appointment LEFT JOIN customer ON appointment.cust_id = customer.cust_id`;
        let [result] = await db.execute(sql);
        res.json({
            success: true,
            message: '获取客户列表成功',
            data: result
        });
    } catch (error) {
        console.error('获取客户列表失败:', error);
        res.status(500).json({
            success: false,
            message: '获取客户列表失败',
            error: error.message
        });
    }
});

// 获取查询项预约项目
router.get("/getProjectIdList", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT DISTINCT appointment.project_id as 'key', project.project_name as 'name' FROM appointment LEFT JOIN project ON appointment.project_id = project.project_id`;
        let [result] = await db.execute(sql);
        res.json({
            success: true,
            message: '获取预约项目成功',
            data: result
        });
    } catch (error) {
        console.error('获取预约项目失败:', error);
        res.status(500).json({
            success: false,
            message: '获取预约项目失败',
            error: error.message
        });
    }
});

// 获取查询项员工list
router.get("/getEmployeeList", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT DISTINCT appointment.emp_id as 'key', employee.emp_name as 'name' FROM appointment LEFT JOIN employee ON appointment.emp_id = employee.emp_id`;
        let [result] = await db.execute(sql);
        res.json({
            success: true,
            message: '获取员工列表成功',
            data: result
        });
    } catch (error) {
        console.error('获取员工列表失败:', error);
        res.status(500).json({
            success: false,
            message: '获取员工列表失败',
            error: error.message
        });
    }
});

// 获取查询项预约状态
router.get("/getAppointmentStatusList", authenticateToken, async (req, res) => {
    try {
        let sql = `SELECT DISTINCT status as 'key', status as 'name' FROM appointment`;
        let [result] = await db.execute(sql);
        res.json({
            success: true,
            message: '获取预约状态成功',
            data: result
        });
    } catch (error) {
        console.error('获取预约状态失败:', error);
        res.status(500).json({
            success: false,
            message: '获取预约状态失败',
            error: error.message
        });
    }
});

// 根据医生id查询预约时间, 用于刚给前端做排除
router.get("/getAppointTime", authenticateToken, async (req, res) => {
    try {
        const { emp_id } = req.query;
        const sql = `SELECT DISTINCT appoint_time FROM appointment WHERE emp_id = ?`;
        const [result] = await db.execute(sql, [emp_id]);
        let appoint_time = [];
        result.forEach(item => {
            appoint_time.push(dayjs(item.appoint_time).format('YYYY-MM-DD HH:mm'));
        });
        res.json({
            success: true,
            message: '获取预约时间成功',
            data: appoint_time
        });
    } catch (error) {
        console.error('获取预约时间失败:', error);
        res.status(500).json({
            success: false,
            message: '获取预约时间失败',
            error: error.message
        });
    }
});

module.exports = router;